package com.baturu.vin.hyundai.dal.dao;

import com.baturu.vin.hyundai.dto.MappingV2;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

/**
 * @Author: chenjiahao
 * @Time: 2018/11/2
 */
public interface DealPartsDAO {

    @Select({"select max(id) from hyundai_parts_en"})
    Integer queryPartsMaxId();

    @Update({"UPDATE hyundai_parts_en",
            "set dealfinalPartsNote = substring_index(finalPartsNote,'[',1) ",
            "WHERE id >= ${start} and id <= ${end}"})
    void updateFinalPartsNoteWithLimit(@Param("start") Integer start, @Param("end") Integer end);

    @Update({"UPDATE hyundai_parts_en",
            "set dealVehicleCode = substr(vehicleCode,INSTR(vehicleCode,'[')+1,INSTR(vehicleCode,']')-INSTR(vehicleCode,'[')-1)",
            "WHERE id >= ${start} and id <= ${end}"})
    void updateVehicleCodeWithLimit(@Param("start") Integer start, @Param("end") Integer end);

    @Update({"UPDATE hyundai_parts",
            "set dealfinalPartsNote = replace(dealfinalPartsNote,'自动变速驱动桥','AUTO TRANSAXLE') ," ,
            " dealfinalPartsNote = replace(dealfinalPartsNote,'低配','LOW GRADE'),",
            " dealfinalPartsNote = replace(dealfinalPartsNote,'高配','HIGH GRADE'),",
            " dealfinalPartsNote = replace(dealfinalPartsNote,'欧洲','EUROPE'),",
            " dealfinalPartsNote = replace(dealfinalPartsNote,'涡轮增压器','TURBO CHARGE'),",
            " dealfinalPartsNote = replace(dealfinalPartsNote,'中东','MIDDLE EAST'),",
            " dealfinalPartsNote = replace(dealfinalPartsNote,'一般','GENERAL')",
            "WHERE id >= ${start} and id <= ${end}"})
    void replaceFinalPartsNoteCH(@Param("start") Integer start, @Param("end") Integer end);

    @Select("select max(id) from hyundai_parts")
    int findHyundaiPartsMaxId();
    @Select("select max(id) from dc_parts_info_hyundaikia")
    int findHyundaiDcPartsMaxId();

    @Update("update hyundai_parts t\n" +
            "set parts_id = ifnull(md5(concat(t.bpno,groupCode,groupNumber,finalPartsCode,classify,groupName,finalPartsName,epcRemark,count)),'')\n" +
            "where t.id >=${start} and t.id<${end};")
    void updatePartsId(@Param("start") int start, @Param("end") int i);

//    @Insert({"insert into dc_parts_info_hyundaikia (parts_id,brand,dir_list,image_id,image_code,position,parts_code,main_group_name,sub_group_name,epc_name,epc_desc,`status`,install_num,btr_parts_name)\n" +
//            "SELECT DISTINCT parts_id,\n" +
//            "if(t1.epcBrand='H','现代','起亚')  brand,t.bpno dir_list,concat(groupcode,imgX,imgY,\"_\",t.bpno) image_id,groupcode image_code ,groupNumber position, finalpartscode parts_code,\n" +
//            " classify main_group_name,groupname sub_group_name,finalpartsname epc_name,epcremark epc_desc,1 `status`,count install_num,ifnull(t2.btr_parts_name,'')\n" +
//            "FROM `hyundai_parts` t\n" +
//            "LEFT JOIN hyundai_model_detail t1 on t.bpno = t1.bpno \n" +
//            "LEFT JOIN dc_rule_parts_name_hyundaikia t2 on t2.parts_code=t.finalPartsCode \n" +
//            "where classify is not null and t.id >=${start} and t.id<${end}",
//            "on DUPLICATE key update btr_parts_name = values(btr_parts_name)",})
//    void insertParts(@Param("start") int start, @Param("end") int i);

    @Insert({"${sql}"})
    void insertParts(@Param("sql") String sql);

    @Update("update dc_parts_info_hyundaikia t1, tmp_parts_mapping t2\n" +
            "set t1.btr_parts_name = t2.btrPartsName\n" +
            "where t1.parts_code = t2.partsCode \n" +
            "and t1.id>=${start} and t1.id <${end}")
    void updateDcParts(@Param("start") int start, @Param("end") int i);

    @Select({"SELECT dir_list,parts_id,btr_parts_name FROM dc_parts_info_hyundaikia t1",
            "WHERE t1.id>=${start} and t1.id <${end} and btr_parts_Name !=''",})
    List<MappingV2> queryDcParts(@Param("start") int start, @Param("end") int i);

    @Insert({"<script>INSERT ignore INTO hyundaikia_parts_mapping_v2 (dir_list,parts_id,btr_parts_name) values",
            "<foreach item='item' collection = 'dtos' open='(' separator='),(' close = ')'>",
            "#{item.dirList},#{item.partsId},#{item.btrPartsName}",
            "</foreach>",
            "</script>",
    })
    void insertMapping(@Param("dtos") List<MappingV2> parts);
}
